/**
 * 
 */
package com.primestone.cim.service;

import java.io.ObjectStreamException;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.sql.DataSource;
import javax.sql.rowset.serial.SerialClob;
import javax.sql.rowset.serial.SerialException;

import ch.iec.tc57._2011.getmeterreadingsmessage.GetMeterReadingsRequestType;
import ch.iec.tc57._2011.schema.message.HeaderType;

import com.google.gson.Gson;
import com.primestone.cim.entities.tmp.ResMeterReadings;
import com.primestone.cim.meterreadings.beans.CIMDBRegister;
import com.primestone.cim.meterreadings.beans.MaxMinDate;
import com.primestone.cim.meterreadings.beans.MaxMinDateForDB;
import com.primestone.cim.meterreadings.beans.Param;
import com.primestone.cim.meterreadings.beans.ReadingsQueryResult;
import com.primestone.cim.meterreadings.beans.ResMeterReadingsContainer;
import com.primestone.cim.meterreadings.util.CIMCodeUtil;
import com.primestone.cim.meterreadings.util.MaxMinDateUtil;
import com.primestone.cim.util.CollectionUtil;
import com.primestone.cim.util.DateUtil;
import com.primestone.cim.util.NumberUtil;
import com.primestone.cim.util.StringUtil;
import com.primestone.cim.util.enums.SQLQueryEnum;
import com.primestone.cim.util.enums.SocDevEnum;
import com.primestone.cim.util.exceptions.ExceptionMessageEnum;
import com.primestone.cim.util.exceptions.ServiceException;

/**
 * @author cesar.mateus
 *
 */
public class GetMeterReadingsService implements IGetMeterReadingsService {

	private static final long serialVersionUID = 4219502105713486600L;

	private final static Logger LOGGER = Logger.getLogger(GetMeterReadingsService.class.getName());
	
	private DataSource dataSource;

	private volatile static IGetMeterReadingsService instance = null;

	private GetMeterReadingsService(){}

	private GetMeterReadingsService(DataSource dataSource) {
		this.dataSource = dataSource;
		this.populateMapCIMEnums();
//		Set<Integer> keySet = mapCIMEnums.keySet();
//		for (Integer key : keySet) {
//			System.out.println("---- key: " + key + mapCIMEnums.get(key));
//		}
	}
	
	/**
	 * 
	 * @param dataSource
	 * @return
	 */
	public static IGetMeterReadingsService getInstance(DataSource dataSource) {
		if (instance == null) {
			synchronized(GetMeterReadingsService.class){
				if (instance == null) {
					instance = new GetMeterReadingsService(dataSource);
				}
			}
		} 
		return instance;
	}
	
	/**
	 * @return
	 */
	private String populateMapCIMEnums() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String msg = null;
		try {
			conn = this.dataSource.getConnection();
			ps = conn.prepareStatement(SQLQueryEnum.SELECT_ALL_CIMFIELDVALUES.getQuery());
			ps.setEscapeProcessing(true);
			ps.setQueryTimeout(3600);

			rs = ps.executeQuery();

			if (!rs.isClosed()) {
				while (rs.next()) {
					CIMCodeUtil.populateMap(
							rs.getInt(1), 
							new CIMDBRegister(
									rs.getInt(1),
									rs.getInt(2),
									rs.getString(3),
									rs.getString(4),
									rs.getInt(5)
									));
				}
			}
		} catch (Exception e) {
			//e.printStackTrace();
			msg = StringUtil.composeMessageServiceException(ExceptionMessageEnum.PROBLEM_POPULATING_CIM_DICTIONARY.getMessage(), e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();	
				}
				if( conn != null ) {
					conn.close();
				}
			} catch (SQLException e) {
				// ignore
				//e.printStackTrace();
			}
		}
		return msg;
	}

	@Override
	public String isValidUser(String user, String pwd) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String msg = null;
		try {
			conn = this.dataSource.getConnection();
			ps = conn.prepareStatement(SQLQueryEnum.SELECT_1_BY_IDUSER_PWD.getQuery());
			ps.setEscapeProcessing(true);
			ps.setQueryTimeout(3600);
			ps.setString(1, user);
			ps.setString(2, pwd);

			rs = ps.executeQuery();

			boolean ok = false;
			if (!rs.isClosed() && rs.next()) {				
				if (StringUtil.safe(rs.getString(1))) {
					ok = true;	
				}
			}

			// The user was not found
			if (!ok) {
				msg = StringUtil._NULL_STRING_;
			}
		} catch (Exception e) {
			//e.printStackTrace();
			msg = StringUtil.composeMessageServiceException(ExceptionMessageEnum.PROBLEM_QUERYING_DATA_USER.getMessage(), e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();	
				}
				if( conn != null ) {
					conn.close();
				}
			} catch (SQLException e) {
				// ignore
				//e.printStackTrace();
			}
		}
		return msg;
	}

	@Override
	public String isValidDevice(String noins) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String msg = null;
		try {
			conn = this.dataSource.getConnection();
			ps = conn.prepareStatement(SQLQueryEnum.SELECT_1_BY_NOINS.getQuery());
			ps.setEscapeProcessing(true);
			ps.setQueryTimeout(3600);
			ps.setString(1, noins);

			rs = ps.executeQuery();

			boolean ok = false;
			if (!rs.isClosed() && rs.next()) {				
				if (StringUtil.safe(rs.getString(1))) {
					ok = true;	
				}
			}

			// The device was not found
			if (!ok) {
				msg = StringUtil._NULL_STRING_;
			}
		} catch (Exception e) {
			//e.printStackTrace();
			msg = StringUtil.composeMessageServiceException(ExceptionMessageEnum.PROBLEM_QUERYING_NOINS.getMessage(), e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();	
				}
				if( conn != null ) {
					conn.close();
				}
			} catch (SQLException e) {
				// ignore
				//e.printStackTrace();
			}
		}
		return msg;
	}

	@Override
	public String isValidSocket(String idSocket) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String msg = null;
		try {
			conn = this.dataSource.getConnection();
			ps = conn.prepareStatement(SQLQueryEnum.SELECT_1_BY_IDSOCKET.getQuery());
			ps.setEscapeProcessing(true);
			ps.setQueryTimeout(3600);
			ps.setString(1, idSocket);

			rs = ps.executeQuery();

			boolean ok = false;
			if (!rs.isClosed() && rs.next()) {				
				if (StringUtil.safe(rs.getString(1))) {
					ok = true;	
				}
			}

			// The socket was not found
			if (!ok) {
				msg = StringUtil._NULL_STRING_;
			}
		} catch (Exception e) {
			//e.printStackTrace();
			msg = StringUtil.composeMessageServiceException(ExceptionMessageEnum.PROBLEM_QUERYING_SOCKETS.getMessage(), e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();	
				}
				if( conn != null ) {
					conn.close();
				}
			} catch (SQLException e) {
				// ignore
				//e.printStackTrace();
			}
		}
		return msg;
	}

	@Override
	public void saveExportRegister(List<MaxMinDate> maxMinDates, String ticket, Long idBlock) throws ServiceException {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = this.dataSource.getConnection();
			//conn.setAutoCommit(false);

			ps = conn.prepareStatement(SQLQueryEnum.INSERT_LASTEXPORTCIM.getQuery());
			ps.setEscapeProcessing(true);

			String socketOrDevice;
			if (CollectionUtil.safe(maxMinDates)) {
				java.sql.Timestamp sqlDateNow = DateUtil.getJavaSqlTimestampFromJavaUtilDate();
				socketOrDevice = maxMinDates.get(0).getSocketOrDevice();
				Map<String, MaxMinDateForDB> mapMaxMinDatesForDB = MaxMinDateUtil.transformMaxMinDate(maxMinDates);
				Set<String> keySetForDB = mapMaxMinDatesForDB.keySet();
				MaxMinDateForDB maxMinDateForDB;
				if (socketOrDevice.equals(SocDevEnum.WORK_DEVICES.getCode())) {
					for (String key : keySetForDB) {
						maxMinDateForDB = mapMaxMinDatesForDB.get(key);
						ps.setString(1, key);
						ps.setNull(2, java.sql.Types.VARCHAR);
						ps.setTimestamp(3, sqlDateNow);
						ps.setTimestamp(4, sqlDateNow);

						if (DateUtil.safe(maxMinDateForDB.getMaxDateM())) {
							ps.setTimestamp(5, DateUtil.getJavaSqlTimestampFromJavaUtilDate(maxMinDateForDB.getMaxDateM()));	
						} else {
							ps.setNull(5, java.sql.Types.TIMESTAMP);
						}

						if (DateUtil.safe(maxMinDateForDB.getMaxDateR())) {
							ps.setTimestamp(6, DateUtil.getJavaSqlTimestampFromJavaUtilDate(maxMinDateForDB.getMaxDateR()));	
						} else {
							ps.setNull(6, java.sql.Types.TIMESTAMP);
						}

						if (DateUtil.safe(maxMinDateForDB.getMaxDateV())) {
							ps.setTimestamp(7, DateUtil.getJavaSqlTimestampFromJavaUtilDate(maxMinDateForDB.getMaxDateV()));	
						} else {
							ps.setNull(7, java.sql.Types.TIMESTAMP);
						}

						ps.setString(8, ticket);
						ps.setLong(9, idBlock);

						ps.addBatch();	
					}	
				} else if (socketOrDevice.equals(SocDevEnum.WORK_SOCKETS.getCode())) {
					for (String key : keySetForDB) {
						maxMinDateForDB = mapMaxMinDatesForDB.get(key);
						ps.setNull(1, java.sql.Types.VARCHAR);
						ps.setString(2, key);
						ps.setTimestamp(3, sqlDateNow);
						ps.setTimestamp(4, sqlDateNow);

						if (DateUtil.safe(maxMinDateForDB.getMaxDateM())) {
							ps.setTimestamp(5, DateUtil.getJavaSqlTimestampFromJavaUtilDate(maxMinDateForDB.getMaxDateM()));	
						} else {
							ps.setNull(5, java.sql.Types.DATE);
						}

						if (DateUtil.safe(maxMinDateForDB.getMaxDateR())) {
							ps.setTimestamp(6, DateUtil.getJavaSqlTimestampFromJavaUtilDate(maxMinDateForDB.getMaxDateR()));	
						} else {
							ps.setNull(6, java.sql.Types.DATE);
						}

						if (DateUtil.safe(maxMinDateForDB.getMaxDateV())) {
							ps.setTimestamp(7, DateUtil.getJavaSqlTimestampFromJavaUtilDate(maxMinDateForDB.getMaxDateV()));	
						} else {
							ps.setNull(7, java.sql.Types.DATE);
						}

						ps.setString(8, ticket);
						ps.setLong(9, idBlock);

						ps.addBatch();	
					}				
				}
			}

			ps.executeBatch();
			//conn.commit();
		} catch (Exception e) {
			if( conn != null ) {
				try {
					conn.rollback();
				} catch (SQLException e1) {
					//e1.printStackTrace();
				}	
			}
			//e.printStackTrace();
			throw new ServiceException(ExceptionMessageEnum.PROBLEM_SAVING_LAST_EXPORT_DATA, e);
		} finally {
			try {
				if (ps != null) {
					ps.close();	
				}
				if( conn != null ) {
					conn.close();
				}
			} catch (SQLException e) {
				// ignore
				//e.printStackTrace();
			}
		}
	}

	/**
	 * Este metodo debe manejar todas las excepciones que lleguen hasta el, ya que se trata de una ultima instancia para el procesamiento asincronico.
	 */
	@Override
	public ResMeterReadingsContainer getReadings(
			GetMeterReadingsRequestType request, int top, String ticket, HeaderType header, int offset, boolean onlyNew, 
			String idSocketOrDevice, SocDevEnum socketOrDevice, Map<String, Date> allFromDates, ServiceException se) {
		Connection conn = null;
		ResMeterReadingsContainer container = null;		
		List<ReadingsQueryResult> ticketReadings = new ArrayList<ReadingsQueryResult>();
		try {
			
			if (se != null) {
				throw se;
			}
			
			conn = this.dataSource.getConnection();

			Param param = new Param(request.getStartTime(), request.getEndTime(), request.getOption());
			
			if (param.getVarType().contains(StringUtil.VARTYPE_LP)) {
				composeTicketReadings(conn, param, StringUtil.VARTYPE_LP, ticketReadings, top, offset, onlyNew, idSocketOrDevice, socketOrDevice, allFromDates.get(StringUtil.VARTYPE_LP));
			} 
			
			if (param.getVarType().contains(StringUtil.VARTYPE_REG)) {
				composeTicketReadings(conn, param, StringUtil.VARTYPE_REG, ticketReadings, top, offset, onlyNew, idSocketOrDevice, socketOrDevice, allFromDates.get(StringUtil.VARTYPE_REG));
			} 
			
			if (param.getVarType().contains(StringUtil.VARTYPE_EVE)) {
				composeTicketReadings(conn, param, StringUtil.VARTYPE_EVE, ticketReadings, top, offset, onlyNew, idSocketOrDevice, socketOrDevice, allFromDates.get(StringUtil.VARTYPE_EVE));
			}

			LOGGER.log(Level.FINE, "---- size ticket readings: {0}", ticketReadings.size());
			if (CollectionUtil.safe(ticketReadings)) {
				container = manageGetReadingsUsualResult(ticket, header.getUser().getUserID(), ticketReadings);
			} else {
				container = manageGetReadingsEmptyResult(ticket, header.getUser().getUserID(), ticketReadings);
			}
			
		} catch( SQLException sqle ) {
			container = manageGetReadingsException(ticket, header.getUser().getUserID(), ticketReadings, ExceptionMessageEnum.PROBLEM_OBTAINING_READINGS_SQL_TYPE, sqle);
			//sqle.printStackTrace();
		} catch( Exception e ) {
			container = manageGetReadingsException(ticket, header.getUser().getUserID(), ticketReadings, ExceptionMessageEnum.PROBLEM_OBTAINING_READINGS_GENERAL_TYPE, e);
			//e.printStackTrace();
		} finally {
			if( conn != null ) {
				try {
					conn.close();
				} catch( SQLException e ) {
					//e.printStackTrace();
				}
			}
		}

		return container;
	}

	/**
	 * 
	 * @param ticket
	 * @param username
	 * @param ticketReadings
	 * @return
	 * @throws SerialException
	 * @throws SQLException
	 */
	private static ResMeterReadingsContainer manageGetReadingsUsualResult(String ticket, String username, List<ReadingsQueryResult> ticketReadings) throws SerialException, SQLException {
		String jsonFromMap = composeJSON(ticket, ticketReadings);
		Clob jsonClob = new SerialClob(jsonFromMap.toCharArray());
		ResMeterReadings resMeterReading = new ResMeterReadings(ticket, username, new Date(), jsonClob);
		return new ResMeterReadingsContainer(resMeterReading, false, false);
	}

	/**
	 * 
	 * @param ticket
	 * @param username
	 * @param messageEnum
	 * @param e
	 * @return
	 */
	private static ResMeterReadingsContainer manageGetReadingsException(String ticket, String username, List<ReadingsQueryResult> ticketReadings, ExceptionMessageEnum messageEnum, Throwable e) {
		ResMeterReadings resMeterReading = null;
		try {
			String jsonFromMap = composeJSON(ticket, ticketReadings, messageEnum, e);
			resMeterReading = new ResMeterReadings( ticket, username, new Date(), new SerialClob( jsonFromMap.toCharArray() ) );
		} catch (Exception e1) {
			// Se ignora esta posible excepcion para evitar mensajes venenosos.
			//e1.printStackTrace();
		}
		return new ResMeterReadingsContainer(resMeterReading, true, true);
	}
	
	/**
	 * 
	 * @param ticket
	 * @param username
	 * @param ticketReadings
	 * @param messageEnum
	 * @param e
	 * @return
	 */
	private static ResMeterReadingsContainer manageGetReadingsEmptyResult(String ticket, String username, List<ReadingsQueryResult> ticketReadings) throws SerialException, SQLException {
		ResMeterReadings resMeterReading = null;
		String jsonFromMap = composeJSON(ticket, ticketReadings, ExceptionMessageEnum.READINGS_NOT_FOUND.getMessage());
		resMeterReading = new ResMeterReadings( ticket, username, new Date(), new SerialClob( jsonFromMap.toCharArray() ) );
		return new ResMeterReadingsContainer(resMeterReading, true, false);
	}

	/**
	 * Colocar los resultados en formato json.
	 * @param ticket
	 * @param ticketReadings
	 * @param messageEnum
	 * @param e
	 * @param isException 
	 * @return
	 */
	private static String composeJSON(String ticket, List<ReadingsQueryResult> ticketReadings, ExceptionMessageEnum messageEnum, Throwable e) {
		ReadingsQueryResult messageResult = new ReadingsQueryResult( StringUtil.composeMessageServiceException(messageEnum.getMessage(), e) );
		ticketReadings.add(messageResult);
		return composeJSON(ticket, ticketReadings);
	}
	
	/**
	 * 
	 * @param ticket
	 * @param ticketReadings
	 * @param message
	 * @return
	 */
	private static String composeJSON(String ticket, List<ReadingsQueryResult> ticketReadings, String message) {
		ReadingsQueryResult messageResult = new ReadingsQueryResult(StringUtil.composeMessageServiceException(message));
		ticketReadings.add(messageResult);
		return composeJSON(ticket, ticketReadings);
	}

	/**
	 * 
	 * @param ticket
	 * @param ticketReadings
	 * @return
	 */
	private static String composeJSON(String ticket, List<ReadingsQueryResult> ticketReadings) {
		Map<String, List<ReadingsQueryResult>> mapforJSon = new HashMap<String, List<ReadingsQueryResult>>();
		mapforJSon.put(ticket, ticketReadings);

		Gson gson = new Gson();
		LOGGER.log(Level.FINEST, "---- map res: {0}", mapforJSon);
		return gson.toJson(mapforJSon);
		//System.out.println("---- json res:" +json);
	}

	/**
	 * 
	 * @param conn
	 * @param param
	 * @param varType
	 * @param ticketReadings
	 * @param top
	 * @param offset
	 * @param onlyNew
	 * @param idSocketOrDevice
	 * @param socketOrDevice
	 * @param from
	 * @return
	 * @throws ServiceException
	 */
	private static List<ReadingsQueryResult> composeTicketReadings (
			Connection conn, Param param, String varType, List<ReadingsQueryResult> ticketReadings, int top, int offset, 
			boolean onlyNew, String idSocketOrDevice, SocDevEnum socketOrDevice, Date from) throws ServiceException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {

			if (onlyNew && !DateUtil.safe(from)) {
				LOGGER.log(Level.INFO, "---- no date, from: {0}, vartype: {1}", new Object[]{from, varType});
				return ticketReadings;
			}

			StringBuilder queryBuilder = new StringBuilder("select top (?) t1.* from ( ");			

			if (varType.equals(StringUtil.VARTYPE_LP)) {
				
				queryBuilderLP(queryBuilder, socketOrDevice, param);
				
				ps = conn.prepareStatement(queryBuilder.toString());
				ps.setEscapeProcessing(true);
				ps.setQueryTimeout(3600);

				int indice = 1;			
				ps.setInt(indice++, top);

				if (onlyNew) {
					ps.setTimestamp(indice++, DateUtil.getJavaSqlTimestampFromJavaUtilDate(from));
					ps.setTimestamp(indice++, DateUtil.getMaxJavaSqlTimestamp());
				} else {
					ps.setTimestamp(indice++, DateUtil.getJavaSqlTimestampFromGregorianCalendar(param.getStartTime().toGregorianCalendar()));

					if (DateUtil.safe(param.getEndTime())) {
						ps.setTimestamp(indice++, DateUtil.getJavaSqlTimestampFromGregorianCalendar(param.getEndTime().toGregorianCalendar()));	
					} else {
						ps.setTimestamp(indice++, DateUtil.getMaxJavaSqlTimestamp());
					}
				}
				
				ps.setString(indice++, idSocketOrDevice);
				
				if (StringUtil.safe(param.getClient())) {
					ps.setString(indice++, param.getClient());
				}
				
				if (StringUtil.safe(param.getDescVar())) {
					ps.setString(indice++, param.getDescVar());
				}
				
				if (StringUtil.safe(param.getUom())) {
					ps.setString(indice++, param.getUom());
				}
				
				if (NumberUtil.safe(param.getIntChannel())) {
					ps.setInt(indice++, param.getIntChannel());
				}
				
				ps.setInt(indice++, offset);
				ps.setInt(indice++, offset + top);
				
			} else if (varType.equals(StringUtil.VARTYPE_REG)) {
				
				queryBuilderReg(queryBuilder, socketOrDevice, param);
								
				ps = conn.prepareStatement(queryBuilder.toString());
				ps.setEscapeProcessing(true);
				ps.setQueryTimeout(3600);

				int indice = 1;			
				ps.setInt(indice++, top);

				if (onlyNew) {
					ps.setTimestamp(indice++, DateUtil.getJavaSqlTimestampFromJavaUtilDate(from));
					ps.setTimestamp(indice++, DateUtil.getMaxJavaSqlTimestamp());
				} else {
					ps.setTimestamp(indice++, DateUtil.getJavaSqlTimestampFromGregorianCalendar(param.getStartTime().toGregorianCalendar()));

					if (DateUtil.safe(param.getEndTime())) {
						ps.setTimestamp(indice++, DateUtil.getJavaSqlTimestampFromGregorianCalendar(param.getEndTime().toGregorianCalendar()));	
					} else {
						ps.setTimestamp(indice++, DateUtil.getMaxJavaSqlTimestamp());
					}
				}
				
				ps.setString(indice++, idSocketOrDevice);
				
				if (StringUtil.safe(param.getClient())) {
					ps.setString(indice++, param.getClient());
				}
				
				if (StringUtil.safe(param.getDescVar())) {
					ps.setString(indice++, param.getDescVar());
				}
				
				if (StringUtil.safe(param.getUom())) {
					ps.setString(indice++, param.getUom());
				}
				
				ps.setInt(indice++, offset);
				ps.setInt(indice++, offset + top);
				
			} else if (varType.equals(StringUtil.VARTYPE_EVE)) {
				
				queryBuilderEve(queryBuilder, socketOrDevice, param);
								
				ps = conn.prepareStatement(queryBuilder.toString());
				ps.setEscapeProcessing(true);
				ps.setQueryTimeout(3600);

				int indice = 1;			
				ps.setInt(indice++, top);

				if (onlyNew) {
					ps.setTimestamp(indice++, DateUtil.getJavaSqlTimestampFromJavaUtilDate(from));
					ps.setTimestamp(indice++, DateUtil.getMaxJavaSqlTimestamp());
				} else {
					ps.setTimestamp(indice++, DateUtil.getJavaSqlTimestampFromGregorianCalendar(param.getStartTime().toGregorianCalendar()));

					if (DateUtil.safe(param.getEndTime())) {
						ps.setTimestamp(indice++, DateUtil.getJavaSqlTimestampFromGregorianCalendar(param.getEndTime().toGregorianCalendar()));	
					} else {
						ps.setTimestamp(indice++, DateUtil.getMaxJavaSqlTimestamp());
					}
				}
				
				ps.setString(indice++, idSocketOrDevice);
				
				if (StringUtil.safe(param.getClient())) {
					ps.setString(indice++, param.getClient());
				}
				
				if (StringUtil.safe(param.getDescVar())) {
					ps.setString(indice++, param.getDescVar());
				}
				
				ps.setInt(indice++, offset);
				ps.setInt(indice++, offset + top);
			}

			if (ps != null) {
				rs = ps.executeQuery();

				if (!rs.isClosed()) {
					ReadingsQueryResult readingsQueryResult;
					String selectedCIMCode;
					while( rs.next() ) {
						selectedCIMCode = CIMCodeUtil.selectCIMCode(
								CIMCodeUtil.composeCIMCodeFromDBValues(rs.getString(12), rs.getString(13), rs.getString(14), rs.getString(15), rs.getString(16), rs.getString(17), 
										rs.getString(18), rs.getString(19), rs.getString(20), rs.getString(21), rs.getString(22) ), 
								CIMCodeUtil.composeCIMCodeFromDBValues(rs.getString(23), rs.getString(24), rs.getString(25), rs.getString(26), rs.getString(27), rs.getString(28), 
										rs.getString(29), rs.getString(30), rs.getString(31), rs.getString(32), rs.getString(33) ),
								rs.getString(7));
						
						readingsQueryResult = new ReadingsQueryResult(
								rs.getInt(1), //rowid
								rs.getString(2), //idsocket
								rs.getString(3), //idClient
								rs.getString(4), //iddevice
								rs.getString(5), //uom
								rs.getString(6), //channel
								rs.getString(7), //descvar
								rs.getString(8), //interval
								rs.getString(9), //ke
								rs.getTimestamp(10), //datatime
								String.valueOf(rs.getFloat(11)), //usagedata
								varType, //varType,
								//variable cim code o dictionary cim code o idvar
								selectedCIMCode,
								CIMCodeUtil.composeCIMAliasFromCIMCode(selectedCIMCode, rs.getString(7)) // cim aliasname
								);

						ticketReadings.add(readingsQueryResult);
					}
				}	
			}
			
			//LOGGER.log(Level.FINE, "---- size ticketReadings {0}", ticketReadings.size());
		} catch( SQLException sqle ) {
			//sqle.printStackTrace();
			throw new ServiceException(ExceptionMessageEnum.PROBLEM_COMPOSING_TICKET_READINGS_SQL_TYPE, sqle);
		} catch( Exception e ) {
			//e.printStackTrace();
			throw new ServiceException(ExceptionMessageEnum.PROBLEM_COMPOSING_TICKET_READINGS_GENERAL_TYPE, e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();	
				}
			} catch (SQLException e) {
				// Ignore
				//e.printStackTrace();
			}
		}

		return ticketReadings;
	}
	
	@Override
	public Map<String, Date> getAllFromDates(String idSocketOrDevice, String varTypes, Date startDateParam) throws ServiceException {
		Connection conn = null;
		Map<String, Date> mapAllDates = new HashMap<String, Date>();
		try {
			conn = this.dataSource.getConnection();
			if (varTypes.contains(StringUtil.VARTYPE_LP)) {
				mapAllDates.put(StringUtil.VARTYPE_LP, getFromDate(conn, idSocketOrDevice, StringUtil.VARTYPE_LP, startDateParam));
			} 
			
			if (varTypes.contains(StringUtil.VARTYPE_REG)) {
				mapAllDates.put(StringUtil.VARTYPE_REG, getFromDate(conn, idSocketOrDevice, StringUtil.VARTYPE_REG, startDateParam));
			} 
			
			if (varTypes.contains(StringUtil.VARTYPE_EVE)) {
				mapAllDates.put(StringUtil.VARTYPE_EVE, getFromDate(conn, idSocketOrDevice, StringUtil.VARTYPE_EVE, startDateParam));
			}
		} catch( SQLException sqle ) {
			//sqle.printStackTrace();
			throw new ServiceException(ExceptionMessageEnum.PROBLEM_OBTAINING_DATES_SQL_TYPE, sqle);
		} catch( Exception e ) {
			//e.printStackTrace();
			throw new ServiceException(ExceptionMessageEnum.PROBLEM_OBTAINING_DATES_GENERAL_TYPE, e);
		} finally {
			if( conn != null ) {
				try {
					conn.close();
				} catch( SQLException e ) {
					//e.printStackTrace();
				}
			}
		}

		LOGGER.log(Level.FINE, "---- calculated all from dates: {0}", mapAllDates);
		return mapAllDates;
	}

	/**
	 * 
	 * @param conn
	 * @param idSocketOrDevice
	 * @param varType
	 * @param startDateParam
	 * @return
	 * @throws ServiceException
	 */
	private static Date getFromDate(Connection conn, String idSocketOrDevice, String varType, Date startDateParam) throws ServiceException {
		Date from = null;
		Date lastCIMExport = getLastCIMExportByNoinsByVarType(conn, idSocketOrDevice, varType);
		Date lastRead = getLastReadByNoinsByVarType(conn, idSocketOrDevice, varType);
		if (!DateUtil.safe(lastRead)) {
			throw new ServiceException(ExceptionMessageEnum.PROBLEM_QUERYING_LAST_READ_DATE.getMessage());
		}

		if (!DateUtil.safe(lastCIMExport) && DateUtil.safe(lastRead)) {
			if (DateUtil.safe(startDateParam)) {
				from = startDateParam;
			} else {
				// LastRead menos n meses (Constante DateUtil.MONTHS_BACKWARD)
				from = DateUtil.getLastReadMonthsAgo(lastRead);
			}
		} else if (lastRead.after(lastCIMExport)) { // Comparar la fecha de ultima exportacion CIM con la fecha de ultima lectura.
			from = lastCIMExport;
		}
		return from;
	}

	/**
	 * 
	 * @param idDevice
	 * @param varType
	 * @return
	 * @throws Exception
	 */
	private static Date getLastReadByNoinsByVarType(Connection conn, String idDevice, String varType) throws ServiceException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Date lastReadDate = null;
		try {
			// Averiguar la ultima fecha de lectura (ultima lectura guardada en la base de datos) para el device y tipo de variable dados.
			String sqlQuery = null;
			if (varType.equals(StringUtil.VARTYPE_LP)) {
				sqlQuery = SQLQueryEnum.SELECT_LASTMMDATETIME_LASTREAD_BY_NOINS.getQuery();
			} else if (varType.equals(StringUtil.VARTYPE_REG)) {
				sqlQuery = SQLQueryEnum.SELECT_LASTREGDATETIME_LASTREAD_BY_NOINS.getQuery();
			} else if (varType.equals(StringUtil.VARTYPE_EVE)) {
				sqlQuery = SQLQueryEnum.SELECT_LASTEVEDATETIME_LASTREAD_BY_NOINS.getQuery();
			}

			ps = conn.prepareStatement(sqlQuery);
			ps.setEscapeProcessing(true);
			ps.setQueryTimeout(3600);
			ps.setString(1, idDevice);			

			rs = ps.executeQuery();

			if (!rs.isClosed() && rs.next()) {				
				lastReadDate = rs.getTimestamp(1);
			}
		} catch (Exception e) {
			//e.printStackTrace();
			throw new ServiceException(ExceptionMessageEnum.PROBLEM_QUERYING_LAST_EXPORT_CIM_DATE, e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();	
				}
			} catch (SQLException e) {
				// ignore
				//e.printStackTrace();
			}
		}
		return lastReadDate;
	}

	/**
	 * 
	 * @param idDevice
	 * @param varType
	 * @return
	 * @throws Exception
	 */
	private static Date getLastCIMExportByNoinsByVarType(Connection conn, String idDevice, String varType) throws ServiceException {
		PreparedStatement ps = null;
		ResultSet rs = null;
		Date lastExportCIMDate = null;
		try {
			// Averiguar la ultima fecha de exportacion para el device y tipo de variable dados.
			String sqlQuery = null;
			if (varType.equals(StringUtil.VARTYPE_LP)) {
				sqlQuery = SQLQueryEnum.SELECT_MAX_MMDATETIME_LASTEXPORTCIM_BY_NOINS.getQuery();
			} else if (varType.equals(StringUtil.VARTYPE_REG)) {
				sqlQuery = SQLQueryEnum.SELECT_MAX_REGDATETIME_LASTEXPORTCIM_BY_NOINS.getQuery();
			} else if (varType.equals(StringUtil.VARTYPE_EVE)) {
				sqlQuery = SQLQueryEnum.SELECT_MAX_EVEDATETIME_LASTEXPORTCIM_BY_NOINS.getQuery();
			}

			ps = conn.prepareStatement(sqlQuery);
			ps.setEscapeProcessing(true);
			ps.setQueryTimeout(3600);
			ps.setString(1, idDevice);			

			rs = ps.executeQuery();

			if (!rs.isClosed() && rs.next()) {				
				lastExportCIMDate = rs.getTimestamp(1);
			}
		} catch (Exception e) {
			//e.printStackTrace();
			throw new ServiceException(ExceptionMessageEnum.PROBLEM_QUERYING_LAST_EXPORT_CIM_DATE, e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				if (ps != null) {
					ps.close();	
				}
			} catch (SQLException e) {
				// ignore
				//e.printStackTrace();
			}
		}
		return lastExportCIMDate;
	}
	
	private static void queryBuilderLP(StringBuilder queryBuilder, SocDevEnum socketOrDevice, Param param) {

		queryBuilder
		.append("select row_number() over ( order by lpr.datetime, rd.idvar, rd.channel ) as rowid, ");
		
		if (socketOrDevice.equals(SocDevEnum.WORK_SOCKETS) || StringUtil.safe(param.getClient())) {
			queryBuilder
			.append("s.idsocket as socket_id, ")
			.append("s.idclient as client_id, ");
		} else {
			queryBuilder
			.append("null as socket_id, ")
			.append("null as client_id, ");
		}
		 
		queryBuilder
		.append("rd.noins as device_id, ")
		.append("rd.uom as uom, ")
		.append("rd.channel as channel, ")
		.append("rd.idvar as id_var, ")
		.append("rd.nointervals as interval, ")
		.append("rd.ke as ke, ")
		.append("lpr.datetime as datetime, ")
		.append("lpr.usage_data as usage_data, ")
		
		.append("dv.cim01 as var_cim01, dv.cim02 as var_cim02, dv.cim03 as var_cim03, dv.cim04 as var_cim04, dv.cim05 as var_cim05, ")
		.append("dv.cim06 as var_cim06, dv.cim07 as var_cim07, dv.cim08 as var_cim08, dv.cim09 as var_cim09, dv.cim10 as var_cim10, ")
		.append("dv.cim11 as var_cim11, ")
		
		.append("vd.cim01 as dict_cim01, vd.cim02 as dict_cim02, vd.cim03 as dict_cim03, vd.cim04 as dict_cim04, vd.cim05 as dict_cim05, ")
		.append("vd.cim06 as dict_cim06, vd.cim07 as dict_cim07, vd.cim08 as dict_cim08, vd.cim09 as dict_cim09, vd.cim10 as dict_cim10, ")
		.append("vd.cim11 as dict_cim11 ")
		
		.append("from reading_detail as rd ")
		.append("inner join load_profile_reading as lpr on lpr.id_reading_detail = rd.id_reading_detail ")
		
		.append("left outer join variable_dictionary vd on vd.idvar = rd.idvar and vd.vartype = 'M' ")
		.append("left outer join device_variables dv on dv.seqnum_var_dict = vd.seqnum and dv.noins = rd.noins ");
		
		if (socketOrDevice.equals(SocDevEnum.WORK_SOCKETS) || StringUtil.safe(param.getClient())) {
			queryBuilder
			.append("inner join config c on rd.noins = c.noins and rd.channel = c.channel ")
			.append("inner join device_socket_dates dsd on dsd.noins = c.noins ")	
			.append("inner join sockets s on s.idsocket = dsd.idsocket ")
			.append("where c.vartype = 'M' ")
			.append("and lpr.datetime between ? and ? ")
			.append("and lpr.datetime >= dsd.start_time ")
			.append("and lpr.datetime <= dsd.end_time ");
		} else {
			queryBuilder
			.append("where lpr.datetime between ? and ? ");
		}
		
		if (socketOrDevice.equals(SocDevEnum.WORK_SOCKETS)) {
			queryBuilder
			.append("and dsd.idsocket = ? ");
		} else {
			queryBuilder
			.append("and rd.noins = ? ");
		}
		
		if (StringUtil.safe(param.getClient())) {
			queryBuilder
			.append("and s.idclient = ? ");
		}
		
		if (StringUtil.safe(param.getDescVar())) {
			queryBuilder
			.append("and rd.idvar = ? ");
		}
		
		if (StringUtil.safe(param.getUom())) {
			queryBuilder
			.append("and rd.uom = ? ");
		}
		
		if (NumberUtil.safe(param.getIntChannel())) {
			queryBuilder
			.append("and rd.channel= ? ");
		}
		
		queryBuilder
		.append(") as t1 ")
		.append("where t1.rowid between ? and ? ")
		.append("order by t1.datetime, t1.id_var, t1.channel ");
		
		LOGGER.log(Level.FINEST, "---- queryBuilder: {0}", queryBuilder);
	}
	
	private static void queryBuilderReg(StringBuilder queryBuilder, SocDevEnum socketOrDevice, Param param) {
		queryBuilder
		.append("select row_number() over ( order by rr.datetime, c.description ) as rowid, ");
		if (socketOrDevice.equals(SocDevEnum.WORK_SOCKETS) || StringUtil.safe(param.getClient())) {
			queryBuilder
			.append("s.idsocket as socket_id, ")
			.append("s.idclient as client_id, ");
		} else {
			queryBuilder
			.append("null as socket_id, ")
			.append("null as client_id, ");
		}
		
		queryBuilder
		.append("rr.noins as device_id, ")
		.append("rr.uom as uom, ")
		.append("null as channel, ")
		.append("c.description as var_description, ")
		.append("null as interval, ")
		.append("null as ke, ")
		.append("rr.datetime as datetime, ")
		.append("rr.readval as usage_data, ")
		
		.append("dv.cim01 as var_cim01, dv.cim02 as var_cim02, dv.cim03 as var_cim03, dv.cim04 as var_cim04, dv.cim05 as var_cim05, ")
		.append("dv.cim06 as var_cim06, dv.cim07 as var_cim07, dv.cim08 as var_cim08, dv.cim09 as var_cim09, dv.cim10 as var_cim10, ")
		.append("dv.cim11 as var_cim11, ")
		
		.append("vd.cim01 as dict_cim01, vd.cim02 as dict_cim02, vd.cim03 as dict_cim03, vd.cim04 as dict_cim04, vd.cim05 as dict_cim05, ")
		.append("vd.cim06 as dict_cim06, vd.cim07 as dict_cim07, vd.cim08 as dict_cim08, vd.cim09 as dict_cim09, vd.cim10 as dict_cim10, ")
		.append("vd.cim11 as dict_cim11 ")
		
		.append("from readregs as rr ")
		.append("inner join config as c on rr.noins = c.noins and rr.idvar = c.idvar ")
		
		.append("left outer join variable_dictionary vd on vd.idvar = c.idvar and vd.vartype = 'R' ")
		.append("left outer join device_variables dv on dv.seqnum_var_dict = vd.seqnum and dv.noins = c.noins ");
		
		if (socketOrDevice.equals(SocDevEnum.WORK_SOCKETS) || StringUtil.safe(param.getClient())) {
			queryBuilder
			.append("inner join device_socket_dates dsd on dsd.noins = c.noins ")	
			.append("inner join sockets s on s.idsocket = dsd.idsocket ")
			.append("where c.vartype = 'R' and rr.datetime between ? and ? ")
			.append("and rr.datetime >= dsd.start_time ")
			.append("and rr.datetime <= dsd.end_time ");
		} else {
			queryBuilder
			.append("where rr.datetime between ? and ? ");
		}
		
		if (socketOrDevice.equals(SocDevEnum.WORK_SOCKETS)) {
			queryBuilder
			.append("and dsd.idsocket = ? ");
		} else {
			queryBuilder
			.append("and rr.noins = ? ");
		}
		
		if (StringUtil.safe(param.getClient())) {
			queryBuilder
			.append("and s.idclient = ? ");
		}
		
		if (StringUtil.safe(param.getDescVar())) {
			queryBuilder
			.append("and c.description = ? ");
		}
		
		if (StringUtil.safe(param.getUom())) {
			queryBuilder
			.append("and rr.uom = ? ");
		}
		
		queryBuilder
		.append(") as t1 ")
		.append("where t1.rowid between ? and ? ")
		.append("order by t1.datetime, t1.var_description ");
		
		LOGGER.log(Level.FINEST, "---- queryBuilder: {0}", queryBuilder);
	}
	
	private static void queryBuilderEve(StringBuilder queryBuilder, SocDevEnum socketOrDevice, Param param) {
		queryBuilder
		.append("select row_number() over ( order by q.datetime, c.description ) as rowid, ");
		
		if (socketOrDevice.equals(SocDevEnum.WORK_SOCKETS) || StringUtil.safe(param.getClient())) {
			queryBuilder
			.append("s.idsocket as socket_id, ")
			.append("s.idclient as client_id, ");
		} else {
			queryBuilder
			.append("null as socket_id, ")
			.append("null as client_id, ");
		}
		
		queryBuilder
		.append("q.noins as device_id, ")
		.append("null as uom, ")
		.append("null as channel, ")
		.append("c.description as var_description, ")
		.append("null as interval, ")
		.append("null as ke, ")
		.append("dateadd(millisecond, msec, q.datetime) as datetime,")
		.append("q.readval as usage_data, ")
		
		.append("dv.cim01 as var_cim01, dv.cim02 as var_cim02, dv.cim03 as var_cim03, dv.cim04 as var_cim04, dv.cim05 as var_cim05, ")
		.append("dv.cim06 as var_cim06, dv.cim07 as var_cim07, dv.cim08 as var_cim08, dv.cim09 as var_cim09, dv.cim10 as var_cim10, ")
		.append("dv.cim11 as var_cim11, ")
		
		.append("vd.cim01 as dict_cim01, vd.cim02 as dict_cim02, vd.cim03 as dict_cim03, vd.cim04 as dict_cim04, vd.cim05 as dict_cim05, ")
		.append("vd.cim06 as dict_cim06, vd.cim07 as dict_cim07, vd.cim08 as dict_cim08, vd.cim09 as dict_cim09, vd.cim10 as dict_cim10, ")
		.append("vd.cim11 as dict_cim11 ")
		
		.append("from quality as q ")
		.append("inner join config as c on q.noins = c.noins and q.idvar = c.idvar ")
		
		.append("left outer join variable_dictionary vd on vd.idvar = c.idvar and vd.vartype = 'V' ")
		.append("left outer join device_variables dv on dv.seqnum_var_dict = vd.seqnum and dv.noins = c.noins ");
		
		if (socketOrDevice.equals(SocDevEnum.WORK_SOCKETS) || StringUtil.safe(param.getClient())) {
			queryBuilder
			.append("inner join device_socket_dates dsd on dsd.noins = c.noins ")	
			.append("inner join sockets s on s.idsocket = dsd.idsocket ")
			.append("where c.vartype = 'V' and q.datetime between ? and ? ")
			.append("and q.datetime >= dsd.start_time ")
			.append("and q.datetime <= dsd.end_time ");
		} else {
			queryBuilder
			.append("where q.datetime between ? and ? ");
		}
		
		if (socketOrDevice.equals(SocDevEnum.WORK_SOCKETS)) {
			queryBuilder
			.append("and dsd.idsocket = ? ");
		} else {
			queryBuilder
			.append("and q.noins = ? ");
		}
		
		if (StringUtil.safe(param.getClient())) {
			queryBuilder
			.append("and s.idclient = ? ");
		}
		
		if (StringUtil.safe(param.getDescVar())) {
			queryBuilder
			.append("and c.description = ? ");
		}
		
		queryBuilder
		.append(") as t1 ")
		.append("where t1.rowid between ? and ? ")
		.append("order by t1.datetime, t1.var_description ");
		
		LOGGER.log(Level.FINEST, "---- queryBuilder: {0}", queryBuilder);
	}
	
	/**
	 * @throws ObjectStreamException  
	 */
	private Object readResolve() throws ObjectStreamException {
		return instance;
	}

	@Override
	protected Object clone() {
		return instance;
	}
}
